<?php
namespace App\Repositories\GameServer;

use App\Models\GameServer\PlayRecord;
use Carbon\Carbon;
use DB;

/**
* 玩家游戏记录
*/
class PlayRecordRepository
{
    //获取某玩家游戏记录
    public function getPlayRecords($id)
    {
        $play_records = PlayRecord::where('account_id', $id)->with('room')->orderBy('id', 'desc')->get();

        // dd($play_records);
        return $play_records;
    }

    //有效用户数 = 玩牌局数>=1的用户
    public static function getActiveGamerNum()
    {
        $res = PlayRecord::select('account_id')->groupBy('account_id')->get()->count();

        return $res;
    }

    //有效活跃用户 -- 需要去重
    public static function getValidActiveGamers($where)
    {
        $sub = PlayRecord::select('account_id', DB::raw('FROM_UNIXTIME(enter_time,"'.$where['format'].'") as time'))
                        ->whereBetween('enter_time', [$where['start'], $where['end']])
                        ->distinct();

        $gamers = DB::connection('game_server')
                    ->table(DB::raw("({$sub->toSql()}) as sub"))
                    ->mergeBindings($sub->getQuery()) // 绑定参数，否则sql语句会只有'?'
                    ->select('time', DB::raw('COUNT(*) as num'))
                    ->groupBy('time')
                    ->orderBy('time', 'asc')
                    ->get();

        return $gamers;
    }

    //有效活跃用户 -- 渠道时间分组
    public function getValidActiveGamerOfChannel($where)
    {
        $channel = $where['channel'];

        $gamers = PlayRecord::select('distributer_id',DB::raw('FROM_UNIXTIME(enter_time,"%Y-%m-%d") as date, COUNT(distinct(account_id)) as count'))
                            ->when($channel, function ($query) use ($channel) {
                                return $query->whereIn('distributer_id', $channel);
                            })
                            ->whereBetween('enter_time', [$where['start'], $where['end']])
                            ->groupBy('distributer_id', 'date')
                            ->get();
        return $gamers;
    }

    //玩牌局数
    public static function getPlayRecordNum($where)
    {
        $gamers = PlayRecord::select(DB::raw('FROM_UNIXTIME(enter_time, "'.$where['format'].'") as time, COUNT(*) as num'))
                            ->whereBetween('enter_time', [$where['start'], $where['end']])
                            ->groupBy('time')
                            ->orderBy('time', 'asc')
                            ->get();
        return $gamers;
    }

    //玩牌局数--渠道
    public static function getPlayRecordNumOfChannel($where)
    {
        $res = PlayRecord::select('distributer_id', DB::raw('COUNT(*) as count'))
                        ->whereBetween('enter_time', [$where['start'], $where['end']])
                        ->groupBy('distributer_id')
                        ->get();
        return $res;
    }

    //玩牌平均数 = 玩牌玩家/玩牌局数
    public static function getPlayRecordAvg($where)
    {
        $avg = PlayRecord::select(DB::raw('COUNT(*) / COUNT(distinct(account_id)) as num, FROM_UNIXTIME(enter_time,"'.$where['format'].'") as time'))
                        ->whereBetween('enter_time', [$where['start'], $where['end']])
                        ->groupBy('time')
                        ->get();
        return $avg;
    }

    //玩牌平均数--渠道
    public static function getPlayRecordAvgOfChannel($where)
    {
        $avg = PlayRecord::select('distributer_id', DB::raw('COUNT(*) / COUNT(distinct(account_id)) as avg'))
                        ->whereBetween('enter_time', [$where['start'], $where['end']])
                        ->groupBy('distributer_id')
                        ->get();
        return $avg;
    }

    //玩牌中位数 = 每个玩家玩的局数, 出现次数最多的值
    public static function getPlayRecordMedian($where)
    {

    }

    //获得玩牌玩家ID
    public static function getGamerIds($where)
    {
        $ids = PlayRecord::select('account_id')
                            ->whereBetween('enter_time', [$where['start'], $where['end']])
                            ->groupBy('account_id')
                            ->get();
        return $ids;
    }
}